package model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.SessionFactory;

import util.hibernate.HibernateUtil;

import databean.Product;



public class ProductDAO {
	
	
	public ProductDAO() {
		addDummyData();
	}
	
	
	public void createProduct(Product product) {
		
		org.hibernate.classic.Session session = null;
		
		try {
			
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			
			Connection connection = session.connection();
			Statement statement = connection.createStatement();
			ResultSet result;

			//checking if same entry is already in database
			String query = "select productID from product where name='"+product.getName()+"'";
			result = statement.executeQuery(query);
			
			//yes - ignore; no - add a new email record
			if (result.next() == true) {
				//ignore
			} else {
				//start inserting
				org.hibernate.Transaction tx = session.beginTransaction();
	        	session.saveOrUpdate(product);
				tx.commit();
			}

			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.flush();
			session.close();
		}
	}
	
	public Product[] getAllProducts() {
		
		Product[] products = null;
		org.hibernate.classic.Session session = null;
		
		try {
			
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();

			Criteria crit = session.createCriteria(Product.class);
			
			@SuppressWarnings("unchecked")
			List<Product> dbProducts = crit.list();

			products = new Product[dbProducts.size()];
			int num = 0;
			
			for(Iterator<Product> it = dbProducts.iterator();it.hasNext();){
				Product product = (Product) it.next();
				
				products[num] = new Product();
				products[num].setProductID(product.getProductID());
				products[num].setName(product.getName());
				products[num].setDescription(product.getDescription());
				num++;
				
			}
			

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.flush();
			session.close();
		}
		
		return products;
	}
	
	
	public void addDummyData() {
		
		Product product;

		//1st dummy data
		product = new Product();
		product.setName("Annex Watchphone S200");
		product.setDescription(null);
		createProduct(product);

		
		//2nd dummy data		
		product = new Product();
		product.setName("WiFi Detector Watch");
		product.setDescription(null);
		createProduct(product);
    	
		
		//3rd dummy data
		product = new Product();
		product.setName("Turtle TW-800 GSM Cell Phone Watch");
		product.setDescription(null);
		createProduct(product);
    	
		
		//4th dummy data
		product = new Product();
		product.setName("Gibson Matrix");
		product.setDescription(null);
		createProduct(product);
		
    	
		//5th dummy data
		product = new Product();
		product.setName("Mr. Gadget 1GB USB Executive Watch");
		product.setDescription(null);
		createProduct(product);
		
		//6th dummy data
		product = new Product();
		product.setName("DX Uncrushable C100");
		product.setDescription(null);
		createProduct(product);
		
		//7th dummy data
		product = new Product();
		product.setName("Garmin Forerunner 405 Watch");
		product.setDescription(null);
		createProduct(product);
		
		//8th dummy data
		product = new Product();
		product.setName("Binary Time Watch");
		product.setDescription(null);
		createProduct(product);
		
		//9th dummy data
		product = new Product();
		product.setName("W100 Mobile Phone Wrist Watch");
		product.setDescription(null);
		createProduct(product);
		
		//10th dummy data
		product = new Product();
		product.setName("WizMatch S100");
		product.setDescription(null);
		createProduct(product);
		
		//11th dummy data - demo product
		product = new Product();
		product.setName("World Slimmest Watch Phone");
		product.setDescription(null);
		createProduct(product);
		
		//12th dummy data -croks
		product = new Product();
		product.setName("Pinky Mallika");
		product.setDescription(null);
		createProduct(product);
	}
	
	
	
	
	public ArrayList<Product> getAssociatedProducts(long userID) throws java.sql.SQLException{
		
		ArrayList<Product> products = new ArrayList<Product>();
		org.hibernate.classic.Session session = null;
		
		try {
			
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			
			Connection connection = session.connection();
			Statement statement = connection.createStatement();
			ResultSet result;
			
			String query = "select productID from user where userID='"+userID+"'";
			result = statement.executeQuery(query);
			
			while(result.next()){
				
				String list = result.getString("productID");
				String[] productIDs = list.split(",");
				
				for (int i = 0; i<productIDs.length; i++) {
					
					Product product = getProduct(i);
					
					product.setProductID(result.getLong("productID"));
					product.setName(result.getString("name"));
					product.setDescription(result.getString("description"));
											
					products.add(product);
					
				}
				
			}
						
			statement.close();
			result.close();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.flush();
			session.close();
		}
		
		return products;
	}
	
	
	public Product getProduct(long productID) throws java.sql.SQLException{
		
		Product product = new Product();
		org.hibernate.classic.Session session = null;
		
		try {
			
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			
			Connection connection = session.connection();
			Statement statement = connection.createStatement();
			ResultSet result;
			
			String query = "select * from product where productID='"+productID+"'";
			result = statement.executeQuery(query);
			result.next();
			
			product.setProductID(productID);
			product.setName(result.getString("name"));
			product.setDescription(result.getString("description"));

			statement.close();
			result.close();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.flush();
			session.close();
		}
		
		return product;
	}


	public long getProductIDByName(String name) throws java.sql.SQLException{
		
		long productID = -1;
		org.hibernate.classic.Session session = null;
		
		try {
			
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			
			Connection connection = session.connection();
			Statement statement = connection.createStatement();
			ResultSet result;
			
			String query = "select productID from product where name='"+name+"'";
			result = statement.executeQuery(query);
			result.next();
			
			productID = result.getLong("productID");
						
			statement.close();
			result.close();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.flush();
			session.close();
		}
		
		return productID;
	}

}
